﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RCA1._0;
using System.Data;
using System.Data.OleDb;

namespace Royal_1._0_DAL_WS
{
    public class DieuLeSoanDAO : DataProvider
    {
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from DIEULESOAN";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy đối tượng"
        private DieuLeSoanDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();

            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            DieuLeSoanDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new DieuLeSoanDTO();
                tempDTO.IDDieuLeSoan = Convert.ToInt32(dr["IDDieuLeSoan"]);
                tempDTO.MaDieuLe = Convert.ToInt32(dr["MaDieuLe"]);
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.NoiDungSoan = dr["NoiDungSoan"].ToString();

            }
            cn.Close();
            return tempDTO;
        }

        #endregion


        #region "Lấy danh sách"
        private List<DieuLeSoanDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeSoanDTO tempDTO;
            List<DieuLeSoanDTO> dsDIEULESOAN = new List<DieuLeSoanDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeSoanDTO();
                tempDTO.IDDieuLeSoan = Convert.ToInt32(dr["IDDieuLeSoan"]);
                tempDTO.MaDieuLe = Convert.ToInt32(dr["MaDieuLe"]);
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.NoiDungSoan = dr["NoiDungSoan"].ToString();
                tempDTO.TinhTrangDieuLeSoan = Convert.ToInt32(dr["TinhTrangDieuLeSoan"]);
                dsDIEULESOAN.Add(tempDTO);
            }
            return dsDIEULESOAN;
        }


        public List<DieuLeSoanDTO> LayDanhSach()
        {
            String sql = "Select * From DIEULESOAN ";
            return LayDSach(sql);
        }

        public List<DieuLeSoanDTO> LayDanhSachTheoMaCongTy(int MaCongTy)
        {
            String sql = "Select * From DIEULESOAN Where TinhTrangDieuLeSoan = 1 and MaCongTy = " + MaCongTy;
            return LayDSach(sql);
        }

        #endregion


        #region "Lấy danh sách nội dung"
        private List<DieuLeSoanDTO> LayDSachNoiDung(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeSoanDTO tempDTO;
            List<DieuLeSoanDTO> dsDIEULE = new List<DieuLeSoanDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeSoanDTO();
                tempDTO.NoiDungSoan = dr["NoiDungSoan"].ToString();
                dsDIEULE.Add(tempDTO);
            }
            return dsDIEULE;
        }

        private List<DieuLeSoanDTO> LayDSNoiDungDieuLe(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeSoanDTO tempDTO;
            List<DieuLeSoanDTO> dsDIEULESOAN = new List<DieuLeSoanDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeSoanDTO();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.NoiDungSoan = dr["NoiDungSoan"].ToString();
                tempDTO.TinhTrangDieuLeSoan = Convert.ToInt32(dr["TinhTrangDieuLeSoan"]);
                dsDIEULESOAN.Add(tempDTO);
            }
            return dsDIEULESOAN;
        }

        public List<DieuLeSoanDTO> LayDanhSachNoiDung()
        {
            String sql = "Select * From DIEULESOAN ";
            return LayDSachNoiDung(sql);
        }


        public List<DieuLeSoanDTO> LayDSNoiDungDieuLeTheoMaCongTy(int MaCongTy)
        {
            String sql = "Select * From DIEULESOAN Where TinhTrangDieuLeSoan = 1 and MaCongTy = " + MaCongTy;
            return LayDSNoiDungDieuLe(sql);
        }

        #endregion


        #region "Tìm kiếm"
        public DieuLeSoanDTO TimTheoMa(int MaDieuLeSoan)
        {

            string strSQL = "select * from DIEULESOAN Where IDDieuLeSoan =" + MaDieuLeSoan.ToString();
            return LayDoiTuong(strSQL);
        }
        #endregion


        #region "Thêm"
        public void Them(DieuLeSoanDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "insert into DIEULESOAN(MaDieuLe, NoiDungSoan, MaCongTy) values (@MaDieuLe, @NoiDungSoan, @MaCongTy)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDieuLe", OleDbType.Integer).Value = tempDTO.MaDieuLe;
            cmd.Parameters.Add("@NoiDungSoan", OleDbType.LongVarWChar).Value = tempDTO.NoiDungSoan;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Xóa"
        public void Xoa(int MaDieuLeSoan)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "delete from DIEULESOAN Where IDDieuLeSoan = " + MaDieuLeSoan.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Sửa"
        public void Sua(DieuLeSoanDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update DIEULESOAN set MaDieuLe = @MaDieuLe, NoiDungSoan = @NoiDungSoan, MaCongTy =  @MaCongTy where IDDieuLeSoan = @IDDieuLeSoan";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDieuLe", OleDbType.Integer).Value = tempDTO.MaDieuLe;
            cmd.Parameters.Add("@NoiDungSoan", OleDbType.LongVarWChar).Value = tempDTO.NoiDungSoan;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
            cmd.Parameters.Add("@IDDieuLeSoan", OleDbType.Integer).Value = tempDTO.IDDieuLeSoan;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


    }
}